Outbound_Process

 
Documentation generated by Matillion ETL

Job: (0) - Daily One MD Sales Rep Affiliation


Param: MDM LOV

Create Table
Parameter
Value
Schema
${Schema_Default}
New Table Name
MDM_LOV
Create/Replace
Replace
Table Metadata
ALIGNMENT STRUCTURE CODE, Text, 255, None, True, FRANCHISE CODE, Text, 255, None, True, ORG ID PREFIX, Text, 255, None, True, SALES ORG DIVISION CODE, Text, 255, None, True, SALES ORG NAME, Text, 255, None, True, DEFAULT BILL-TO, Text, 255, None, True, DEFAULT PAY-FROM, Text, 255, None, True, UPDATED BY, Text, 255, None, True, UPDATED DATE, date, 255, None, True
Distribution Style
Auto
Sort Key
Primary Key
Identity Columns
Backup Table
Yes
DROP TABLE IF EXISTS "jnjobprod"."MDM_LOV" CASCADE; CREATE TABLE "jnjobprod"."MDM_LOV" ("alignment structure code" VARCHAR(255) NULL, "franchise code" VARCHAR(255) NULL, "org id prefix" VARCHAR(255) NULL, "sales org division code" VARCHAR(255) NULL, "sales org name" VARCHAR(255) NULL, "default bill-to" VARCHAR(255) NULL, "default pay-from" VARCHAR(255) NULL, "updated by" VARCHAR(255) NULL, "updated date" DATE NULL) BACKUP Yes

MDM LOV Load

S3 Load
Parameter
Value
Schema
${Schema_Default}
Target Table Name
mdm_lov
Load Columns
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
S3 URL Location
s3://ethicon/outbound/One MD Testing /Input files/
S3 Object Prefix
MDM_LOV_Param.txt
IAM Role ARN
arn:aws:iam::<aws-account-id>:role/<role-name>
Data File Type
Delimited
Delimiter
|
Explicit IDs
No
S3 Bucket Region
None
Compression Method
None
Encoding
UTF8
Remove Quotes
No
Replace Invalid Characters
?
Maximum Errors
0
Date Format
auto
Time Format
auto
Ignore Header Rows
1
Accept Any Date
Yes
Ignore Blank Lines
Yes
Truncate Columns
No
Fill Record
Yes
Trim Blanks
Yes
NULL As
\\N
Empty As Null
Yes
Blanks As Null
Yes
Comp Update
On
Stat Update
On
Escape
No
Round Decimals
Yes
Manifest
No
COPY "jnjobprod"."mdm_lov" ("alignment structure code", "franchise code", "org id prefix", "sales org division code", "sales org name", "default bill-to", "default pay-from", "updated by", "updated date") FROM 's3://ethicon/outbound/One MD Testing /Input files/MDM_LOV_Param.txt' WITH CREDENTIALS AS 'aws_access_key_id=XXXXXX;aws_secret_access_key=XXXXXX' MAXERROR AS 0 DATEFORMAT AS 'auto' TIMEFORMAT AS 'auto' ACCEPTANYDATE TRIMBLANKS EMPTYASNULL BLANKSASNULL COMPUPDATE ON STATUPDATE ON ROUNDEC DELIMITER AS '|' ENCODING AS UTF8 ACCEPTINVCHARS AS '?' IGNOREHEADER AS 1 IGNOREBLANKLINES FILLRECORD NULL AS '\\N'

Daily-Data Lake ETH MDM21 Sales Rep Affiliation 0

Run Transformation
Parameter
Value
Transformation Job
(1) - Daily-Data Lake ETH MDM21 Sales Rep Affiliation
Set Scalar Variables
Set Grid Variables

(2) - Daily Populate Prior Sale Rep Affiliation 0

Run Transformation
Parameter
Value
Transformation Job
(2) - Daily Populate Prior Sale Rep Affiliation
Set Scalar Variables
Set Grid Variables

OneMD ETH DL MDM21 SALES REP AFFILIATION Unload

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_eth_dl_mdm21_sales_rep_affiliation
S3 URL Location
${ONEMD_S3_Target_Folder}
S3 Object Prefix
ETH MDM21 Sales Rep Affiliation.txt
IAM Role Arn
arn:aws:iam::<aws-account-id>:role/<role-name>
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
No
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None
UNLOAD ('SELECT * FROM "jnjobprod"."outbound_dest_eth_dl_mdm21_sales_rep_affiliation"') TO 's3://ethicon/outbound/Outbound_Process/ONEMD/ETH MDM21 Sales Rep Affiliation.txt' ACCESS_KEY_ID 'XXXXXX' SECRET_ACCESS_KEY 'XXXXXX' PARALLEL OFF ALLOWOVERWRITE DELIMITER AS '|' REGION AS 'us-west-2'

Set Current Table File For Archive

Python Script
Parameter
Value
Script
context.updateVariable('Archive_temp_current_table', 'outbound_dest_eth_dl_mdm21_sales_rep_affiliation')
context.updateVariable('Archive_temp_current_file', 'ETH MDM21 Sales Rep Affiliation.txt')
context.updateVariable('Archive_type','ONEMD')
Interpreter
Python 3
Timeout
360

Archive Outbound Global Function 0

Run Orchestration
Parameter
Value
Orchestration Job
Archive Outbound Global Function
Set Scalar Variables
Set Grid Variables

Job: (1) - Daily-Data Lake ETH MDM21 Sales Rep Affiliation


Participant-Geo Association - Get Fiscal Start Date and End Date

Team-Geo Association - Get Fiscal Start and End Date

TODO - Fix Input table name

Participant Geo Association
- Process Territories
- Process Division

Merge: Territory, Division

Process Data Lake Prior Org

Get Latest Geography Information

Process: MDM LOV Parameters

Post Data to Output table
- Daily - ETH MDM21 Sales Rep Affiliation

- DL Part ID in Sales Rep Affiliation

Process DL Prior Sales Rep Affiliation: Holding Territories not in Org file until the Friday processing.

Param: Geo Hierarchy Structure

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_param_geo_hierarchy_structure
Column Names
upper_algn_struc_cd, lower_algn_struc_cd, efftv_start_dt, efftv_end_dt, level_cd
Trim Columns
No

Param: Geo Hire Filter Territory

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Ilike, Territory
Combine Conditions
AND

Part-Geo: Expand by Upper Geo

Join
Parameter
Value
Main Table
Part-Geo: Filter Territory
Main Table Alias
pgft
Joins
Param: Geo Hire Filter Territory, ghft, Left
Join Expressions
"pgft"."algn_struc_cd" = "ghft"."upper_algn_struc_cd"
AND cast("pgft"."efftv_start_dt" as date) <= cast("ghft"."efftv_end_dt" as date)
AND cast("pgft"."efftv_end_dt" as date) >= cast("ghft"."efftv_start_dt" as date), pgft_Left_ghft
Output Columns
pgft.algn_struc_cd, algn_struc_cd, pgft.participant_id, participant_id, pgft.geo_id, geo_id, pgft.core_temp_cd, core_temp_cd, pgft.split_pct, split_pct, pgft.last_updated_dt, last_updated_dt, pgft.efftv_start_dt, efftv_start_dt, pgft.efftv_end_dt, efftv_end_dt, ghft.lower_algn_struc_cd, lower_algn_struc_cd, ghft.efftv_start_dt, new_efftv_start_dt, ghft.efftv_end_dt, new_efftv_end_dt

Filter: Null Out

Filter
Parameter
Value
Filter Conditions
new_efftv_start_dt, Not, Null
Combine Conditions
AND

Part-Geo: Merge Terr, Div

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
Yes
Remove duplicates
Yes

Part-Geo: Filter Not Null

Filter
Parameter
Value
Filter Conditions
lower_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Part-Geo: Get Upper Last Updated

Join
Parameter
Value
Main Table
Part-Geo: Filter Not Null
Main Table Alias
pgf
Joins
Param: Alignment Structure, pas, Left
Join Expressions
"pgf"."lower_algn_struc_cd" ="pas"."algn_struc_cd", pgf_Left_pas
Output Columns
pgf.participant_id, participant_id, pgf.algn_struc_cd, pgf_algn_struc_cd, pgf.geo_id, geo_id, pgf.core_temp_cd, core_temp_cd, pgf.split_pct, split_pct, pgf.last_updated_dt, pgf_last_updated_dt, pgf.efftv_start_dt, pgf_efftv_start_dt, pgf.efftv_end_dt, pgf_efftv_end_dt, pgf.lower_algn_struc_cd, lower_algn_struc_cd, pgf.new_efftv_start_dt, new_efftv_start_dt, pgf.new_efftv_end_dt, new_efftv_end_dt, pgf.source_table, source_table, pas.last_updated_dt, load_date

Part-Geo: Get Dates for Upper

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
GREATEST (cast("pgf_efftv_start_dt" as date), cast("new_efftv_start_dt" as date)), efftv_start_dt, LEAST (cast("pgf_efftv_end_dt" as date), cast("new_efftv_end_dt" as date)), efftv_end_dt

Part-Geo : Manager

Rename
Parameter
Value
Column Mapping
participant_id, participant_id, lower_algn_struc_cd, algn_struc_cd, geo_id, geo_id, core_temp_cd, core_temp_cd, split_pct, split_pct, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, load_date, last_updated_dt

Part-Geo: Merge Terr - Mgr

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
Yes
Remove duplicates
Yes

Get Sales Rep Teams

Join
Parameter
Value
Main Table
Part-Geo: Merge Terr - Mgr
Main Table Alias
pgtm
Joins
Team-Geo: Adjust Dates, tg, Left
Join Expressions
"pgtm"."algn_struc_cd" = "tg"."algn_struc_cd"
AND "pgtm"."geo_id" = "tg"."geo_id"
AND Cast("pgtm"."efftv_start_dt" as date) <= Cast("tg"."tg_efftv_end_dt" as date)
AND Cast("pgtm"."efftv_end_dt" as date) >= Cast("tg"."tg_efftv_start_dt" as date), pgtm_Left_tg
Output Columns
pgtm.participant_id, participant_id, pgtm.algn_struc_cd, algn_struc_cd, pgtm.geo_id, geo_id, pgtm.core_temp_cd, core_temp_cd, pgtm.split_pct, split_pct, pgtm.efftv_start_dt, efftv_start_dt, pgtm.efftv_end_dt, efftv_end_dt, pgtm.last_updated_dt, last_updated_dt, tg.tg_efftv_start_dt, efftv_start_dt_new, tg.tg_efftv_end_dt, efftv_end_dt_new, tg.team_id, team_id, tg.split_pct, tg_split_pct

Resolve Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
GREATEST (cast("efftv_start_dt" as date), cast("efftv_start_dt_new" as date)), efftv_start_dt, LEAST (cast("efftv_end_dt" as date), cast("efftv_end_dt_new" as date)), efftv_end_dt, CASE WHEN "team_id" IS NULL OR "team_id" = '' THEN
"geo_id"
ELSE
"team_id"
END, team_id, CASE WHEN "split_pct" IS NULL OR "split_pct" = '' THEN
'1.0000'
ELSE
"split_pct"
END, split_pct

Bring in LOV Values

Join
Parameter
Value
Main Table
Part-Geo: Merge Terr - Mgr
Main Table Alias
pgmtm
Joins
MDM LOV Parameters, lov, Inner
Join Expressions
"pgmtm"."algn_struc_cd" = "lov"."alignment structure code", pgmtm_Inner_lov
Output Columns
pgmtm.algn_struc_cd, algn_struc_cd, pgmtm.participant_id, participant_id, pgmtm.geo_id, geo_id, pgmtm.core_temp_cd, core_temp_cd, pgmtm.split_pct, split_pct, pgmtm.efftv_start_dt, efftv_start_dt, pgmtm.efftv_end_dt, efftv_end_dt, pgmtm.last_updated_dt, last_updated_dt, lov.org id prefix, org id prefix, lov.default bill-to, default bill-to, lov.default pay-from, default pay-from

Find new/ changed/ deleted

Join
Parameter
Value
Main Table
Bring in LOV Values
Main Table Alias
lov
Joins
Input: DL Prior Sales Rep Affiliation, psra, Left
Join Expressions
"lov"."algn_struc_cd" = "psra"."algn_struc_cd"
AND "lov"."participant_id" = "psra"."participant_id"
AND Cast("lov"."efftv_start_dt" as date) = Cast("psra"."geo_efftv_start_dt" as date)
AND Cast("lov"."efftv_end_dt" as date) = Cast("psra"."geo_efftv_end_dt" as date)

AND "lov"."org id prefix" = "psra"."org id prefix"
AND "lov"."default bill-to" = "psra"."default bill-to"
AND "lov"."default pay-from" = "psra"."default pay-from", lov_Left_psra
Output Columns
lov.participant_id, lov_participant_id, lov.algn_struc_cd, lov_algn_struc_cd, lov.geo_id, lov_geo_id, lov.core_temp_cd, lov_core_temp_cd, lov.split_pct, lov_split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, lov_org id prefix, lov.default bill-to, lov_default bill-to, lov.default pay-from, lov_default pay-from, psra.participant_id, psra_participant_id, psra.algn_struc_cd, psra_algn_struc_cd, psra.geo_efftv_start_dt, psra_geo_efftv_start_dt, psra.geo_efftv_end_dt, psra_geo_efftv_end_dt, psra.geo_id, psra_geo_id, psra.org id prefix, psra_org id prefix, psra.default bill-to, psra_default bill-to, psra.default pay-from, psra_default pay-from, psra.split_pct, psra_split_pct, psra.core_temp_cd, psra_core_temp_cd

Find new/ changed/ deleted UN-MATCHED RIGHT

Filter
Parameter
Value
Filter Conditions
lov_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Find new/ changed/ deleted UN-MATCHED -Column

Rename
Parameter
Value
Column Mapping
psra_participant_id, participant_id, psra_algn_struc_cd, algn_struc_cd, psra_geo_efftv_start_dt, geo_efftv_start_dt, psra_geo_efftv_end_dt, geo_efftv_end_dt, psra_geo_id, geo_id, psra_org id prefix, org id prefix, psra_default bill-to, default bill-to, psra_default pay-from, default pay-from, lov_split_pct, split_pct, lov_core_temp_cd, core_temp_cd

Check Deleted for Changed

Join
Parameter
Value
Main Table
Find new/ changed/ deleted UN-MATCHED -Column
Main Table Alias
fncdr
Joins
Bring in LOV Values, lov, Left
Join Expressions
cast("fncdr"."geo_efftv_end_dt" as date) <> cast("lov"."efftv_end_dt" as date)
AND cast("fncdr"."geo_efftv_start_dt" as date) = cast("lov"."efftv_start_dt" as date)
AND "fncdr"."algn_struc_cd" = "lov"."algn_struc_cd"
AND "fncdr"."geo_id" = "lov"."geo_id", fncdr_Left_lov
Output Columns
fncdr.participant_id, participant_id, fncdr.algn_struc_cd, algn_struc_cd, fncdr.geo_efftv_start_dt, geo_efftv_start_dt, fncdr.geo_efftv_end_dt, geo_efftv_end_dt, fncdr.geo_id, geo_id, fncdr.org id prefix, org id prefix, fncdr.default bill-to, default bill-to, fncdr.default pay-from, default pay-from, fncdr.split_pct, split_pct, fncdr.core_temp_cd, core_temp_cd, lov.participant_id, lov_participant_id, lov.algn_struc_cd, lov_algn_struc_cd, lov.geo_id, lov_geo_id, lov.core_temp_cd, lov_core_temp_cd, lov.split_pct, lov_split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, lov_org id prefix, lov.default bill-to, lov_default bill-to, lov.default pay-from, lov_default pay-from

Check Deleted for Changed - Matched

Filter
Parameter
Value
Filter Conditions
lov_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Get Ended Territories

Join
Parameter
Value
Main Table
Check Deleted for Changed - Matched
Main Table Alias
cdfc
Joins
DL Prior Org, dlpo, Left
Join Expressions
"cdfc"."algn_struc_cd" = "dlpo"."algn_struc_cd"
AND "cdfc"."geo_id" = "dlpo"."geo_id", cdfc_Left_dlpo
Output Columns
cdfc.participant_id, participant_id, cdfc.algn_struc_cd, algn_struc_cd, cdfc.geo_efftv_start_dt, geo_efftv_start_dt, cdfc.geo_efftv_end_dt, geo_efftv_end_dt, cdfc.geo_id, geo_id, cdfc.org id prefix, org id prefix, cdfc.default bill-to, default bill-to, cdfc.default pay-from, default pay-from, cdfc.split_pct, split_pct, cdfc.core_temp_cd, core_temp_cd, dlpo.geo_id, dlpo_geo_id

Get Ended Territories: Excluded

Filter
Parameter
Value
Filter Conditions
dlpo_geo_id, Is, Null or blank
Combine Conditions
AND

Check Held Territories

Join
Parameter
Value
Main Table
Hold New Territories - Un-Matched
Main Table Alias
hntun
Joins
Get Ended Territories: Excluded, get, Inner
Join Expressions
"hntun"."algn_struc_cd" = "get"."algn_struc_cd"
AND "hntun"."geo_id" = "get"."geo_id"
AND Cast("hntun"."efftv_start_dt" as date) = Cast("get"."geo_efftv_start_dt" as date), hntun_Inner_get
Output Columns
hntun.algn_struc_cd, algn_struc_cd, hntun.participant_id, participant_id, hntun.efftv_start_dt, efftv_start_dt, hntun.efftv_end_dt, efftv_end_dt, hntun.geo_id, geo_id, hntun.org id prefix, org id prefix, hntun.default bill-to, default bill-to, hntun.default pay-from, default pay-from, hntun.split_pct, split_pct, hntun.core_temp_cd, core_temp_cd

Check held Territories: Calculate

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
'N', Delete Indicator

Bring New/Changed and Deleted

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
Yes

Get Territory Level

Join
Parameter
Value
Main Table
Bring New/Changed and Deleted
Main Table Alias
bncd
Joins
Get Latest Geography: Filter Not Nation, glgfa, Inner
Join Expressions
"bncd"."algn_struc_cd" = "glgfa"."algn_struc_cd"
AND "bncd"."geo_id" = "glgfa"."geo_id" , bncd_Inner_glgfa
Output Columns
bncd.participant_id, participant_id, bncd.algn_struc_cd, algn_struc_cd, bncd.geo_id, geo_id, bncd.split_pct, split_pct, bncd.efftv_start_dt, efftv_start_dt, bncd.efftv_end_dt, efftv_end_dt, bncd.delete indicator, delete indicator, bncd.org id prefix, org id prefix, bncd.default bill-to, default bill-to, bncd.default pay-from, default pay-from, bncd.core_temp_cd, core_temp_cd, glgfa.level_cd, level_cd

Get Territory Level - Filter Out X999

Filter
Parameter
Value
Filter Conditions
participant_id, Not, Ilike, X999
Combine Conditions
AND

Create Constants

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
'OPCO_ETH', provider id, "geo_id", organization Id , 'N', primary rep Indicator, cast(getdate() as date), last_updated_dt, 'N', sales rep pay-from Indicator, '', sales rep universal customer n, 'N', sales rep bill-to indicator

Create Constants: Filter Vacant Participant

Filter
Parameter
Value
Filter Conditions
participant_id, Is, Ilike, x999
Combine Conditions
AND

Merge with x999

Unite
Parameter
Value
Method
Overlapping Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
Yes

Merge with x999: Calculate Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
to_char(cast("efftv_start_dt" as date),'yyyymmdd'), efftv_start_dt, to_char(cast("efftv_end_dt" as date),'yyyymmdd'), efftv_end_dt, to_char(cast("last_updated_dt" as date),'yyyymmdd'), last_updated_dt, Cast(Cast("split_pct" as decimal(10,4)) as varchar(255)), split_pct

ETH MDM21 Sales Rep Affiliation: Rename

Rename
Parameter
Value
Column Mapping
provider id, Provider ID, participant_id, World Wide ID, geo_id, Organization ID, efftv_start_dt, Affiliation Effective Date, efftv_end_dt, Affiliation End Date, primary rep indicator, Primary Rep Indicator, delete indicator, Delete Indicator, last_updated_dt, Last Update Date, sales rep bill-to indicator, Sales Rep Bill-to indicator, default bill-to, Bill-To Universal Customer Number, sales rep pay-from indicator, Sales Rep Pay-From Indicator, default pay-from, Pay-From Universal Customer Number, sales rep universal customer n, Sales Rep Universal Customer Number, split_pct, Split Percentage, algn_struc_cd, Account Alignment Structure Code, core_temp_cd, Core Temp Indicator

Sales Rep Affiliation - Distinct

Distinct
Parameter
Value
Columns
provider id, world wide id, organization id, affiliation effective date, affiliation end date, primary rep indicator, delete indicator, last update date, sales rep bill-to indicator, bill-to universal customer number, sales rep pay-from indicator, pay-from universal customer number, sales rep universal customer number, split percentage, account alignment structure code, core temp indicator

Daily - Data Lake ETH MDM21 Sales Rep Affiliation

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
outbound_dest_eth_dl_mdm21_sales_rep_affiliation
Fix Data Type Mismatches
No
Column Mapping
provider id, provider id, world wide id, world wide id, organization id, organization id, affiliation effective date, affiliation effective date, affiliation end date, affiliation end date, primary rep indicator, primary rep indicator, delete indicator, delete indicator, last update date, last update date, sales rep bill-to indicator, sales rep bill-to indicator, bill-to universal customer number, bill-to universal customer number, sales rep pay-from indicator, sales rep pay-from indicator, pay-from universal customer number, pay-from universal customer number, sales rep universal customer number, sales rep universal customer number, split percentage, split percentage, account alignment structure code, account alignment structure code, core temp indicator, core temp indicator
Truncate
Truncate
Automatic Compression
No

Filter Out Not in Sales Roster

Join
Parameter
Value
Main Table
Create Constants
Main Table Alias
cc
Joins
Sales Roster, sr, Left
Join Expressions
"cc"."participant_id" = "sr"."jj_ww_id", cc_Left_sr
Output Columns
cc.participant_id, participant_id, cc.algn_struc_cd, algn_struc_cd, cc.geo_id, geo_id, cc.split_pct, split_pct, cc.efftv_start_dt, efftv_start_dt, cc.efftv_end_dt, efftv_end_dt, cc.delete indicator, delete indicator, cc.org id prefix, org id prefix, cc.default bill-to, default bill-to, cc.default pay-from, default pay-from, cc.core_temp_cd, core_temp_cd, cc.level_cd, level_cd, cc.provider id, provider id, cc.organization id , organization id, cc.primary rep indicator, primary rep indicator, cc.last_updated_dt, last_updated_dt, cc.sales rep pay-from indicator, sales rep pay-from indicator, cc.sales rep universal customer n, sales rep universal customer n, cc.sales rep bill-to indicator, sales rep bill-to indicator, sr.jj_ww_id, wwid

Filter Out Not In Sales Roster: Matched

Filter
Parameter
Value
Filter Conditions
wwid, Not, Null or blank
Combine Conditions
AND

Distinct Participant ID

Distinct
Parameter
Value
Columns
participant_id

Staging: Post to DL Part ID in Sales Rep Affil

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
stg_dl_part_id_in_sales_rep_affil
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id
Truncate
Truncate
Automatic Compression
No

Filter Out Not In Sales Roster: Un-Matched

Filter
Parameter
Value
Filter Conditions
wwid, Is, Null or blank
Combine Conditions
AND

Excluded Not in Roster

Join
Parameter
Value
Main Table
Exclude Future Dated: Include
Main Table Alias
efdm
Joins
Filter Out Not In Sales Roster: Un-Matched, fonsrun, Left
Join Expressions
"efdm"."participant_id" = "fonsrun"."participant_id", efdm_Left_fonsrun
Output Columns
efdm.participant_id, participant_id, efdm.algn_struc_cd, algn_struc_cd, efdm.geo_id, geo_id, efdm.core_temp_cd, core_temp_cd, efdm.split_pct, split_pct, efdm.efftv_start_dt, geo_efftv_start_dt, efdm.efftv_end_dt, geo_efftv_end_dt, efdm.last_updated_dt, last_updated_dt, efdm.org id prefix, org id prefix, efdm.default bill-to, default bill-to, efdm.default pay-from, default pay-from, fonsrun.participant_id, fonsrun_participant_id

Excluded Not in Roster - Matched

Filter
Parameter
Value
Filter Conditions
fonsrun_participant_id, Not, Null or blank
Combine Conditions
AND

Combine Non Future and Original

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
Yes

Rename 0

Rename
Parameter
Value
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from, split_pct, split_pct, core_temp_cd, core_temp_cd, last_updated_dt, last_updated_dt

Calculator 0

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
sysdate, last_updated

Post to DL Prior Sale Rep Affiliation_temp_bkp

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
stg_dl_prior_sale_rep_affiliation_bkp
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from, last_updated, last_updated
Truncate
Append

Post to DL Prior Sale Rep Affiliation_temp

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
stg_dl_prior_sale_rep_affiliation
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from
Truncate
Truncate
Automatic Compression
No

Excluded Not in Roster - Un-Matched

Filter
Parameter
Value
Filter Conditions
fonsrun_participant_id, Is, Null or blank
Combine Conditions
AND

Get Original for Not in Sales

Join
Parameter
Value
Main Table
Excluded Not in Roster - Un-Matched
Main Table Alias
enrun
Joins
Input: DL Prior Sales Rep Affiliation, psra, Inner
Join Expressions
"enrun"."algn_struc_cd" = "psra"."algn_struc_cd"
AND "enrun"."geo_id" = "psra"."geo_id"
AND cast("enrun"."geo_efftv_start_dt" as date) = cast("psra"."geo_efftv_start_dt" as date)
AND "enrun"."participant_id" = "psra"."participant_id", enrun_Inner_psra
Output Columns
enrun.participant_id, participant_id, enrun.algn_struc_cd, algn_struc_cd, enrun.geo_id, geo_id, enrun.core_temp_cd, core_temp_cd, enrun.split_pct, split_pct, enrun.geo_efftv_start_dt, geo_efftv_start_dt, enrun.geo_efftv_end_dt, geo_efftv_end_dt, enrun.last_updated_dt, last_updated_dt, enrun.org id prefix, org id prefix, enrun.default bill-to, default bill-to, enrun.default pay-from, default pay-from, psra.participant_id, psra_participant_id

Check Deleted for Changed - Un-Matched

Filter
Parameter
Value
Filter Conditions
lov_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Check Deleted for Changed : Sequence Cols

Rename
Parameter
Value
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, efftv_start_dt, geo_efftv_end_dt, efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from, split_pct, split_pct, core_temp_cd, core_temp_cd

Check Deleted for Changed - Un-matched: Calculate

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
'N', Delete Indicator

Find new/ changed/ deleted UN-MATCHED LEFT

Filter
Parameter
Value
Filter Conditions
psra_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Find new/ changed/ deleted UN-MATCHED LEFT-Column

Rename
Parameter
Value
Column Mapping
lov_participant_id, participant_id, lov_algn_struc_cd, algn_struc_cd, lov_geo_id, geo_id, lov_core_temp_cd, core_temp_cd, lov_split_pct, split_pct, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, last_updated_dt, last_updated_dt, lov_org id prefix, org id prefix, lov_default bill-to, default bill-to, lov_default pay-from, default pay-from

Identify Future Dated Records

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN Cast("efftv_start_dt" as date) > cast(convert_timezone('EST', Cast(getdate() as date)) as date)
OR Cast("efftv_end_dt" as date) >= cast(convert_timezone('EST', Cast(getdate() as date)) as date)
AND Cast("efftv_end_dt" as date) != Cast('12-31-9999' as date) THEN
'Yes'
ELSE
'No'
END

/*CASE WHEN Cast("efftv_start_dt" as date) > Cast('2019-12-02' as date)
OR (Cast("efftv_end_dt" as date) >= Cast('2019-12-02' as date)
AND Cast("efftv_end_dt" as date) != Cast('12-31-9999' as date)) THEN
'Yes'
ELSE
'No'
END
*/
, Is_Future_Dated_Flag, Cast(getdate() as date), Process Date

Exclude Future Dated Records - Filter - Yes

Filter
Parameter
Value
Filter Conditions
is_future_dated_flag, Is, Ilike, Yes
Combine Conditions
AND

Exclude Future Dated

Join
Parameter
Value
Main Table
Filter New Territories
Main Table Alias
fnt
Joins
Exclude Future Dated Records - Filter - Yes, ifdr, Left
Join Expressions
"fnt"."algn_struc_cd" = "ifdr"."algn_struc_cd"
AND "fnt"."participant_id" = "ifdr"."participant_id"
AND "fnt"."geo_id" = "ifdr"."geo_id"
AND "fnt"."efftv_start_dt" = "ifdr"."efftv_start_dt"
AND "fnt"."efftv_end_dt" = "ifdr"."efftv_end_dt", fnt_Left_ifdr
Output Columns
fnt.participant_id, participant_id, fnt.algn_struc_cd, algn_struc_cd, fnt.geo_id, geo_id, fnt.core_temp_cd, core_temp_cd, fnt.split_pct, split_pct, fnt.efftv_start_dt, efftv_start_dt, fnt.efftv_end_dt, efftv_end_dt, fnt.last_updated_dt, last_updated_dt, fnt.org id prefix, org id prefix, fnt.default bill-to, default bill-to, fnt.default pay-from, default pay-from, ifdr.algn_struc_cd, ifdr_algn_struc_cd

Exclude Future Dated: Exclude

Filter
Parameter
Value
Filter Conditions
ifdr_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Get Original for Future Dated

Join
Parameter
Value
Main Table
Exclude Future Dated: Exclude
Main Table Alias
gofd
Joins
Input: DL Prior Sales Rep Affiliation, dlpsra, Inner
Join Expressions
"gofd"."algn_struc_cd" = "dlpsra"."algn_struc_cd"
AND "gofd"."geo_id" = "dlpsra"."geo_id"
AND "gofd"."efftv_start_dt" = "dlpsra"."geo_efftv_start_dt"
AND "gofd"."efftv_end_dt" = "dlpsra"."geo_efftv_end_dt", gofd_Inner_dlpsra
Output Columns
gofd.participant_id, participant_id, gofd.algn_struc_cd, algn_struc_cd, gofd.geo_id, geo_id, gofd.core_temp_cd, core_temp_cd, gofd.split_pct, split_pct, gofd.efftv_start_dt, geo_efftv_start_dt, gofd.efftv_end_dt, geo_efftv_end_dt, gofd.last_updated_dt, last_updated_dt, gofd.org id prefix, org id prefix, gofd.default bill-to, default bill-to, gofd.default pay-from, default pay-from, dlpsra.participant_id, dlpsra_participant_id

Exclude Future Dated: Include

Filter
Parameter
Value
Filter Conditions
ifdr_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Identify Future Dated Records: Active

SQL
Parameter
Value
SQL Query
SELECT
*
FROM ($T{Identify Future Dated Records})
WHERE
cast(efftv_start_dt as date) <= cast(convert_timezone('EST', cast(getdate() as date)) as date)
AND cast(efftv_end_dt as date) >= cast(convert_timezone('EST', cast(getdate() as date)) as date)

Hold New Territories

Join
Parameter
Value
Main Table
Identify Future Dated Records: Active
Main Table Alias
ifdr
Joins
DL Prior Org, dlpo, Left
Join Expressions
"ifdr"."algn_struc_cd" = "dlpo"."algn_struc_cd"
AND "ifdr"."geo_id" = "dlpo"."geo_id" , ifdr_Left_dlpo
Output Columns
ifdr.algn_struc_cd, algn_struc_cd, ifdr.participant_id, participant_id, ifdr.efftv_start_dt, efftv_start_dt, ifdr.efftv_end_dt, efftv_end_dt, ifdr.geo_id, geo_id, ifdr.org id prefix, org id prefix, ifdr.default bill-to, default bill-to, ifdr.default pay-from, default pay-from, ifdr.split_pct, split_pct, ifdr.core_temp_cd, core_temp_cd, dlpo.algn_struc_cd, dlpo_algn_struc_cd

Hold New Territories - Un-Matched

Filter
Parameter
Value
Filter Conditions
dlpo_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Hold New Territories - Matched

Filter
Parameter
Value
Filter Conditions
dlpo_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Hold New Territories -Matched; Calculate

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
'N', Delete Indicator

Find new/ changed/ deleted MATCHED

Filter
Parameter
Value
Filter Conditions
psra_algn_struc_cd, Not, Null
Combine Conditions
AND

Filter New Territories

Join
Parameter
Value
Main Table
Bring in LOV Values
Main Table Alias
lov
Joins
DL Prior Org (2), dlpo, Inner
Join Expressions
"lov"."algn_struc_cd" = "dlpo"."algn_struc_cd"
AND "lov"."geo_id" = "dlpo"."geo_id", lov_Inner_dlpo
Output Columns
lov.participant_id, participant_id, lov.algn_struc_cd, algn_struc_cd, lov.geo_id, geo_id, lov.core_temp_cd, core_temp_cd, lov.split_pct, split_pct, lov.efftv_start_dt, efftv_start_dt, lov.efftv_end_dt, efftv_end_dt, lov.last_updated_dt, last_updated_dt, lov.org id prefix, org id prefix, lov.default bill-to, default bill-to, lov.default pay-from, default pay-from, dlpo.geo_id, dlpo_geo_id, dlpo.algn_struc_cd, dlpo_algn_struc_cd

Param: Geo Hire Cal Dates Filter Division

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Ilike, Division
Combine Conditions
AND

Part-Geo: Expand by Upper Division

Join
Parameter
Value
Main Table
Part-Geo: Format Dates Filter Division
Main Table Alias
pgfd
Joins
Param: Geo Hire Cal Dates Filter Division, ghfd, Left
Join Expressions
"pgfd"."algn_struc_cd" = "ghfd"."upper_algn_struc_cd"
AND cast("pgfd"."efftv_start_dt" as date) <= cast("ghfd"."efftv_end_dt" as date)
AND cast("pgfd"."efftv_end_dt" as date) >= cast("ghfd"."efftv_start_dt" as date), pgfd_Left_ghfd
Output Columns
pgfd.participant_id, participant_id, pgfd.algn_struc_cd, algn_struc_cd, pgfd.geo_id, geo_id, pgfd.core_temp_cd, core_temp_cd, pgfd.split_pct, split_pct, pgfd.last_updated_dt, last_updated_dt, pgfd.efftv_start_dt, efftv_start_dt, pgfd.efftv_end_dt, efftv_end_dt, ghfd.lower_algn_struc_cd, lower_algn_struc_cd, ghfd.efftv_start_dt, new_efftv_start_dt, ghfd.efftv_end_dt, new_efftv_end_dt

Part-Geo: Upper Div Filter Nulls

Filter
Parameter
Value
Filter Conditions
lower_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Input: Team-Geo Association

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_customerteam_geo_assoc
Column Names
algn_struc_cd, geo_id, team_id, split_pct, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Team-Geo: Rename Columns

Rename
Parameter
Value
Column Mapping
last_updated_dt, last_updated, algn_struc_cd, algn_struc_cd, geo_id, geo_id, team_id, team_id, split_pct, split_pct, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt

Team-Geo: Start Date

Join
Parameter
Value
Main Table
Team-Geo: Rename Columns
Main Table Alias
tga
Joins
Input: Fiscal Calendar 2, fc, Left
Join Expressions
cast("tga"."efftv_start_dt" as date) = cast("fc"."efftv_start_dt" as date), tga_Left_fc
Output Columns
tga.algn_struc_cd, algn_struc_cd, tga.geo_id, geo_id, tga.team_id, team_id, tga.split_pct, split_pct, tga.efftv_start_dt, tga_efftv_start_dt, tga.efftv_end_dt, tga_efftv_end_dt, tga.last_updated, last_updated, fc.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Team-Geo: End Date

Join
Parameter
Value
Main Table
Team-Geo: Start Date
Main Table Alias
tgsd
Joins
Input: Fiscal Calendar 2, fc, Left
Join Expressions
cast("tgsd"."tga_efftv_end_dt" as date) = cast("fc"."efftv_end_dt" as date), tgsd_Left_fc
Output Columns
tgsd.algn_struc_cd, algn_struc_cd, tgsd.geo_id, geo_id, tgsd.team_id, team_id, tgsd.split_pct, split_pct, tgsd.last_updated, last_updated, tgsd.fiscal_year_wk_start_dt, tg_efftv_start_dt, fc.fiscal_year_wk_end_dt, tg_efftv_end_dt

Team-Geo: Adjust Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "tg_efftv_start_dt" is null THEN
to_date('01/01/1900','MM/DD/YYYY')
ELSE
"tg_efftv_start_dt"
END, tg_efftv_start_dt, CASE WHEN "tg_efftv_end_dt" is null THEN
to_date('12/31/9999','MM/DD/YYYY')
ELSE
"tg_efftv_end_dt"
end, tg_efftv_end_dt

Input: Geographies

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_geo_master_outbound
Column Names
algn_struc_cd, geo_id, level_cd, geo_nm, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Geo: Rename

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, geo_id, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, last_updated_dt, last_updated

Geographies - Format Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
Cast("efftv_start_dt" as date), efftv_start_dt, Cast("efftv_end_dt" as date), efftv_end_dt

Aggregate Geographies

Aggregate
Parameter
Value
Groupings
algn_struc_cd, geo_id
Aggregations
efftv_start_dt, Max

Get Latest Geography

Join
Parameter
Value
Main Table
Geographies - Format Dates
Main Table Alias
g
Joins
Aggregate Geographies, ag, Inner
Join Expressions
"g"."algn_struc_cd" = "ag"."algn_struc_cd"
AND "g"."geo_id" = "ag"."geo_id"
AND cast("g"."efftv_start_dt" as date) = cast("ag"."max_efftv_start_dt" as date), g_Inner_ag
Output Columns
g.algn_struc_cd, algn_struc_cd, g.geo_id, geo_id, g.geo_nm, geo_nm, g.level_cd, level_cd, g.efftv_start_dt, efftv_start_dt, g.efftv_end_dt, efftv_end_dt, g.last_updated, last_updated

Get Latest Geography: Filter Active

SQL
Parameter
Value
SQL Query
SELECT
"algn_struc_cd",
"geo_id",
"geo_nm",
"level_cd",
"efftv_start_dt",
"efftv_end_dt",
"last_updated"
FROM ($T{Get Latest Geography})
WHERE
(Cast("efftv_start_dt" as date) <= Cast(getdate() as date)
AND Cast("efftv_end_dt" as date) >= Cast(getdate() as date))

Get Latest Geography: Filter Not Nation

Filter
Parameter
Value
Filter Conditions
level_cd, Not, Ilike, Nation
Combine Conditions
AND

MDM LOV Parameters

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_mdm_lov_param
Column Names
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
Trim Columns
No

Participant-Geo Association

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_participant_geo_assoc
Column Names
algn_struc_cd, participant_id, geo_id, core_temp_cd, split_pct, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Part-Geo: Rename Columns

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, participant_id, participant_id, geo_id, geo_id, core_temp_cd, core_temp_cd, split_pct, split_pct, efftv_start_dt, geo_efftv_start_dt, efftv_end_dt, geo_efftv_end_dt, last_updated_dt, last_updated

Part-Geo: Start Date

Join
Parameter
Value
Main Table
Part-Geo: Rename Columns
Main Table Alias
pga
Joins
Input: Fiscal Calendar, fc, Left
Join Expressions
cast("pga"."geo_efftv_start_dt" as date) = cast("fc"."efftv_start_dt" as date), pga_Left_fc
Output Columns
pga.algn_struc_cd, algn_struc_cd, pga.participant_id, participant_id, pga.geo_id, geo_id, pga.core_temp_cd, core_temp_cd, pga.split_pct, split_pct, pga.last_updated, last_updated, pga.geo_efftv_start_dt, geo_efftv_start_dt, pga.geo_efftv_end_dt, geo_efftv_end_dt, fc.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Part-Geo: End Date

Join
Parameter
Value
Main Table
Part-Geo: Start Date
Main Table Alias
pgsd
Joins
Input: Fiscal Calendar, fc, Left
Join Expressions
cast("pgsd"."geo_efftv_end_dt" as date) = cast("fc"."efftv_end_dt" as date), pgsd_Left_fc
Output Columns
pgsd.algn_struc_cd, algn_struc_cd, pgsd.participant_id, participant_id, pgsd.geo_id, geo_id, pgsd.core_temp_cd, core_temp_cd, pgsd.split_pct, split_pct, pgsd.last_updated, last_updated_dt, pgsd.fiscal_year_wk_start_dt, pg_efftv_start_dt, fc.fiscal_year_wk_end_dt, pg_efftv_end_dt

Part-Geo: Calc Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE WHEN "pg_efftv_start_dt" IS NULL THEN
to_date('01/01/1900' ,'MM/DD/YYYY')
ELSE
"pg_efftv_start_dt"
END

, efftv_start_dt, CASE WHEN "pg_efftv_end_dt" IS NULL THEN
to_date('12/31/9999' ,'MM/DD/YYYY')
ELSE
"pg_efftv_end_dt"
END

, efftv_end_dt, cast("last_updated_dt" as date), last_updated_dt

Part-Geo: Filter Terr

Filter
Parameter
Value
Filter Conditions
geo_id, Is, Like, ____
Combine Conditions
AND

Part-Geo: Filter Territory

Filter
Parameter
Value
Filter Conditions
geo_id, Not, Like, ____
Combine Conditions
AND

Part-Geo: Format Dates Filter Division

Filter
Parameter
Value
Filter Conditions
geo_id, Is, Like, __
Combine Conditions
AND

Input: Fiscal Calendar

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_fiscal_calendar
Column Names
efftv_start_dt, efftv_end_dt, datamonth, fiscal_year_month, fiscal_year_wk_start_dt, fiscal_year_wk_end_dt
Trim Columns
No

Input: Fiscal Calendar 2

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_fiscal_calendar
Column Names
efftv_start_dt, efftv_end_dt, datamonth, fiscal_year_month, fiscal_year_wk_start_dt, fiscal_year_wk_end_dt
Trim Columns
No

Param: Alignment Structure

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_alignment_structures
Column Names
algn_struc_cd, algn_struc_nm, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Input: DL Prior Sales Rep Affiliation

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
dl_prior_sale_rep_affiliation
Column Names
participant_id, algn_struc_cd, geo_efftv_start_dt, geo_efftv_end_dt, geo_id, org id prefix, default bill-to, default pay-from, split_pct, core_temp_cd
Trim Columns
No

DL Prior Org

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_dl_prior_org
Column Names
geo_id, algn_struc_cd
Trim Columns
No

DL Prior Org (2)

Table Input
Parameter
Value
Schema
${Schema_2}
Table Name
outbound_dest_dl_prior_org
Column Names
geo_id, algn_struc_cd
Trim Columns
No

Sales Roster

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_sales_roster
Column Names
reg_no, reg_nm, div_no, div_nm, terr_no, terr_nm, fran_cd, posn_cd, posn_nm, pos_refnc_cd, area_id, first_nm, last_nm, vmail_no, addr_ln_1_txt, apt_no, addr_ln_2_txt, city_nm, stt_cd, postl_cd, tel_no, ssan_no, pgr_1_no, pin_1_no, pgr_2_no, pin_2_no, fax_no, cell_tel_no, sfa_pda_ovrrd, sex_cd, stat_cd, email_addr_text, cstctr_no, bday_dt, hire_dt, spse_nm, spse_ssn_no, chldn_txt, pg_no, actv_ind, jj_ww_id, dendrite_id
Trim Columns
No

Job: (2) - Daily Populate Prior Sale Rep Affiliation


Staging: stg_dl_prior_sale_rep_affiliation

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
stg_dl_prior_sale_rep_affiliation
Column Names
participant_id, algn_struc_cd, geo_efftv_start_dt, geo_efftv_end_dt, geo_id, org id prefix, default bill-to, default pay-from, split_pct, core_temp_cd, last_updated_dt
Trim Columns
No

dl prior sale rep affiliation

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
dl_prior_sale_rep_affiliation
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id, algn_struc_cd, algn_struc_cd, geo_efftv_start_dt, geo_efftv_start_dt, geo_efftv_end_dt, geo_efftv_end_dt, geo_id, geo_id, org id prefix, org id prefix, default bill-to, default bill-to, default pay-from, default pay-from
Truncate
Truncate
Automatic Compression
No

Get Data Staging: stg_dl_prior_sale_rep_affiliation

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
stg_dl_part_id_in_sales_rep_affil
Column Names
participant_id
Trim Columns
No

Post to (Participant) dl part id in sales rep affil

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
dl part id in sales rep affil
Fix Data Type Mismatches
No
Column Mapping
participant_id, participant_id
Truncate
Truncate
Automatic Compression
No

Job: Archive Outbound Global Function


Add TimeStamp & Get File,Table Information from context of the job run

Python Script
Parameter
Value
Script
import datetime

x = datetime.datetime.now()
x=str(x).split('.')

x=x[0]
print('time_stamp :'+str(x))
print('Archive_temp_current_table :'+str(Archive_temp_current_table))
print('Archive_temp_current_file :'+str(Archive_temp_current_file))

Archive_temp_current_file=Archive_temp_current_file+'_'+x+'__'
print('Archive_temp_current_file upt :'+str(Archive_temp_current_file))
context.updateVariable('Archive_temp_current_file', str(Archive_temp_current_file))
print('Archive_type :'+Archive_type)


#dynamically change path for Archive
if Archive_type=='ONEMD':
S3_temp_Archive_Location=S3_ONEMD_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
elif Archive_type=='MDM21':
S3_temp_Archive_Location=S3_MDM21_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
else:
S3_temp_Archive_Location=S3_EUSS_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))




Interpreter
Jython

If ONEMD

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, ONEMD
Combine Conditions
And

S3 Archive(ONEMD)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_ONEMD_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If MDM21

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, MDM21
Combine Conditions
And

S3 Archive(MDM21)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_MDM21_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If EUSS

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, EUSS
Combine Conditions
And

S3 Archive(EUSS)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_EUSS_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None